package database;

import java.sql.*;

import model.Orders;
import oracle.jdbc.pool.OracleDataSource;
import test.Main;

public class DailyOrderOperations {
	
	private Connection conn;
	private Statement stm;
	public ResultSet rst;
	private PreparedStatement prst;
	
	
	public DailyOrderOperations() {
		
		conn=openDB();
	}
	
	// open database connection
	public Connection openDB() {
		
		try {
			
			OracleDataSource ods = new OracleDataSource();
			
			if ( test.Main.dataConn == "GaryTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn == "KeythTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}

			conn = ods.getConnection();
			System.out.println("connected daily ord.");
		} 
		catch (Exception e) {
			
			System.out.print("Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	// result set of Daily orders
		public ResultSet fillTable(int i) {
			try {
				String queryString = "SELECT p.prod_supplier,p.prod_name, sum(o.prod_qty)"+
									" FROM product p, orderitem o,orders ord,day d"+
									" WHERE o.DAY_ID ="+i+
									" AND ord.user_id="+ Main.userId +
									" AND ord.ord_num=d.ord_num"+
									" AND d.day_id=o.day_id"+
									" AND d.ord_num=o.ord_num"+
									" AND p.prod_id = o.prod_id"+
									" GROUP BY p.prod_supplier, p.prod_name"+ 
									" ORDER BY p.prod_supplier";

				stm = conn.createStatement();
				rst = stm.executeQuery(queryString);
			} 
			catch (Exception e) {
				System.out.println(e);
			}
			return rst;
		}
		
		// drop day table from database
		public void dropDayTable() 
		{
			System.out.println("Checking for existing tables.");
			try
			{
				stm = conn.createStatement();
				try
				{
				stm.execute("drop table OrderItem cascade constraints PURGE");
				System.out.println("orderitem table dropped.");
				}
				catch(SQLException ex)
				{
					
				}	
				stm.execute("DROP TABLE Day");
					System.out.println("Day table dropped.");
				
			} 
			catch (SQLException ex) 
			{
				System.out.println("ERROR: " + ex.getMessage());
				//ex.printStackTrace();
			}
		}
		
		// create a day table in database
		public void CreateDayTable() 
		{
			try 
			{
				stm = conn.createStatement();
				String create = "CREATE TABLE Day "
						+ "(ord_num NUMBER, day_id NUMBER, active VARCHAR(10), PRIMARY KEY (ord_num,day_id))";

				stm.executeUpdate(create);
				System.out.println("day table created");
				
			} 
			catch (SQLException e) 
			{
				System.out.print("SQL Exception " + e);
				System.exit(1);
			}
		}
		
		// delete a day from table
				public void deleteDay(int i) 
				{
					try 
					{
						String cmd = "DELETE FROM Day WHERE ord_num =" + i;
						stm = conn.createStatement();
						stm.executeUpdate(cmd);
					} 
					catch (Exception e) 
					{
						System.out.println(e);
					}
				}
				// add a day to the table
				public void addDay(int o,int d,String a) 
				{
					try 
					{
						String sql = "INSERT INTO Day(ord_num,day_id,active) VALUES (?,?,?)";

						prst = conn.prepareStatement(sql);
						prst.setInt(1, o);
						prst.setInt(2, d);
						prst.setString(3, a);
						prst.executeUpdate();
					} 
					catch (Exception se) 
					{
						System.out.println(se);
					}
				}
		// close the connection to database
				public void closeDB() {
					try {
						stm.close();
						rst.close();
						conn.close();
						System.out.print("Connection closed daily ord");
					} 
					catch (SQLException e){
						System.out.print("Could not close connection ");
						e.printStackTrace();
					}
				}
				
				// close the connection to database
				public void closeDB2() {
					try {
						stm.close();
						//rst.close();
						conn.close();
						System.out.print("Connection closed daily ord");
					} 
					catch (SQLException e){
						System.out.print("Could not close connection ");
						e.printStackTrace();
					}
				}
				// close the connection to database
				public void closeDB3() {
					try {
						//stm.close();
						//rst.close();
						conn.close();
						System.out.print("Connection closed daily ord");
					} 
					catch (SQLException e){
						System.out.print("Could not close connection ");
						e.printStackTrace();
					}
				}
}
